const express = require('express');
const router = express.Router();
const R = require("../util/json")
const connection = require("../util/db")
const json = require("../util/jsonFormat")


function extracted(brand, sql, objs) {
    if (brand.parentId) {
        sql += "and b.parent_id = ? ";
        objs.push(Number.parseInt(brand.parentId));
    }
    if (brand.brandName) {
        sql += "and b.brand_name like ? ";
        objs.push(`%${brand.brandName}%`)
    }
    if (brand.firstLetter) {
        sql += "and b.first_letter = ? ";
        objs.push(brand.firstLetter);
    }
    return sql;
}

router.get("/page",function (req, res) {
    let brand = req.query;
    let sql = "select b.*, coalesce(b2.child,0) child from brand b left join (select count(id) child,parent_id from brand group by parent_id) b2 on b.id = b2.parent_id where 1 = 1 ";
    const objs = [];
    sql = extracted(brand, sql, objs);
    sql+="order by b.first_letter,b.id asc ";
    if(brand.parentId == 0 && brand.page > 0){
        sql+="limit ?,?";
        objs.push((brand.page - 1)*brand.limit,parseInt(brand.limit));
    }
    connection.query(sql,objs,function (e,brandList) {
        if(e){
            throw e;
        }
        objs.length=0;
        sql = "select count(b.id) count from brand b left join (select count(id) child,parent_id from brand group by parent_id) b2 on b.id = b2.parent_id where 1 = 1 ";
        sql=extracted(brand,sql,objs);
        connection.query(sql,objs,function (e,r) {
           if(e){
               throw e;
           }
           const count = r[0].count;
           res.json(R.page(count,json.formatArray(brandList)));
        })
    })
})


router.delete("/delete/:id",function (req, res) {
    const id = req.params.id;
    connection.query("select * from brand where parent_id = ?",[id],function (e,r) {
        if(e){
            throw e;
        }
        if(r.length>0){
            res.json(R.error("该品牌存在子级节点，禁止删除！"))
        }else{
            connection.query("delete from brand where id = ?",[id],function (e,r) {
                if(e){
                    throw e;
                }
                res.json(R.ok());
            })
        }
    })
})

router.get("/info/:id",function (req, res) {
    const id = req.params.id;
    connection.query("select * from brand where id = ?",[id],function (e,r) {
        if(e){
            throw e;
        }
        if(r.length>0){
            res.json(R.data(json.formatObject(r[0])))
        }else{
            res.json(R.error("编号异常"))
        }
    })
})

router.post('/update',function (req, res) {
    const {id,brandName,firstLetter,parentId,ps} = req.body;
    if(id){
        let sql = "update brand set brand_name = ?,first_letter = ?,parent_id = ?,ps = ? where id = ?";
        const objs = [brandName,firstLetter,parentId,ps,id];
        connection.query(sql,objs,function (e) {
            if(e){
                throw e;
            }
            res.json(R.ok());
        })
    }else{
        let sql = "insert into brand values(null,?,?,null,?,?)";
        const objs = [brandName,firstLetter,parentId,ps];
        connection.query(sql,objs,function (e) {
            if(e){
                throw e;
            }
            res.json(R.ok());
        })
    }
})










/************************************测试数据，请勿使用**************************************/

router.get("/save10086",function (req, res) {
    connection.query("select * from car_brand_cate",[],function (e,r) {
        let sql = "insert into brand values";
        const objs = [];
        for(const brand of r){
            sql+="(?,?,?,'',0,null),";
            objs.push(brand.id,brand.name,brand.bfirstletter);
        }
        sql=sql.substring(0,sql.length-1);
        connection.query(sql,objs,function (e,r) {
            if(e){
                throw e;
            }
            res.json(R.data(r))
        })
    })
})

router.get("/save20086",function (req, res) {
    connection.query("select * from brand",[],function (e,r) {
        if(e){
            throw e;
        }
        let num = 0;
        for(const brand of r){
            let fn = [];
            connection.query("select * from car_brand where brand_cate_id = ?",[brand.id],function (e,d) {
                if(e){
                    throw e;
                }
                fn=d.map(item=>item.factory_name)
                fn = new Set(fn);
                let sql ="insert into brand values";
                const objs=[];
                for(const f of fn){
                    sql+="(null,?,null,null,?,?),";
                    objs.push(f,brand.id,brand.id);
                }
                sql=sql.substring(0,sql.length-1);
                connection.query(sql,objs,function (e,r) {
                    if(e){
                        throw e;
                    }
                    num+=1;
                    console.log(r)
                })
            })
        }

        const inter = setInterval(function () {
            if(num === r.length){
                console.log("完成")
                res.json(R.ok())
                clearInterval(inter);
            }
        },500)
    })
})

router.get("/save30086",function (req,res) {
    connection.query("select * from brand where parent_id != 0",function (e,r) {
        if(e){
            throw e;
        }
        let num = 0;
        for(const b of r){
            connection.query("select * from car_brand where factory_name = ?",[b.brand_name],function (e,s) {
                if(e){
                    throw e;
                }
                let sql = "insert into brand values";
                const objs=[];
                for(const n of s){
                    sql+="(null,?,null,null,?,?),";
                    objs.push(n.series_name,b.id,b.ps+"-"+b.id);
                }
                sql=sql.substring(0,sql.length-1);
                connection.query(sql,objs,function (e,r) {
                    if(e){
                        throw e;
                    }
                    num++;
                    console.log(r);
                })
            })
        }
        let inter = setInterval(function () {
            if(num===r.length){
                console.log("完成")
                res.json(R.ok());
                clearInterval(inter)
            }
        },500)
    })
})


module.exports = router;
